SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[SP_TMDOCU_CLIE_Q37]
/*-------------------------------------------------------*/
/*---Empresa               OFISIS S.A.                --*/
/*---CLIEnte               SANTILLANA                 --*/
/*---Sistema               Tesoreria                  --*/
/*---M®dulo                Empresa                    --*/
/*---Programa              ESTADO DE CUENTA		       --*/
/*---Script                SP_TMDOCU_CLIE_Q37.sql     --*/
/*---Nombre SP             SP_TMDOCU_CLIE_Q37         --*/
/*---Desarrollado por      Eduardo Vivar Pomiano      --*/
/*---Fecha Creaci®n        03/01/2001                 --*/
/*---Base Datos            SQL			       --*/
/*---Versi®n               2005                        --*/
/*---Invoca a SP                                      --*/
/*-------------------------------------------------------*/

/*-------------------------------------------------------*/
/*---Modificado 1 por     :D.CH.B				 --*/
/*---Fecha Modificaci®n   :26/11/2001 			 --*/ 
/*---Detalle Modificaci®n : ACTUALIZACIONES DE CPPQ	 --*/
/*-------------------------------------------------------*/
/*-------------------------------------------------------*/
/*---Modificado 1 por     :Reynaldo Menacho L.				 --*/
/*---Fecha Modificaci®n   :08/03/2002 			 --*/ 
/*---Detalle Modificaci®n : MANEJO DE VARIAS MONEDAS	 --*/
/*-------------------------------------------------------*/

/*-------------------------------------------------------*/
/*---Drop Proc SP_TMDOCU_CLIE_Q37                      --*/
/*---Grant execute on SP_TMDOCU_CLIE_Q37 to public         --*/ 
/*---	SP_TMDOCU_CLIE_Q37 "Where TMDOCC_GENE.CO_CLIE >= '00000000008' And TMDOCC_GENE.CO_CLIE <= '00000000008'", '', 
"T","E","2000/01/01",
	"2003/08/29","2003/08/29", "1", "C", "D", "SOL", "N", 0                                                        --*/
/*-------------------------------------------------------*/

@ISCA_WHER_0001	VARCHAR(255),
@ISCA_WHER_0002	VARCHAR(255),
@ISTI_SALD	TD_VC_001,
@ISFL_FECH	TD_VC_001,
@IDFE_INIC 	TD_DT_001,
@IDFE_FINA 	TD_DT_001,
@IDFE_CANC	TD_DT_001,
@ISDE_ORDE	TD_VC_001,
@ISTI_AUXI_CLIE	TD_VC_001,
@ISTI_REPO 	TD_VC_003,
@ISCO_MONE_PRES TD_VC_003,
@ISST_USUA	TD_VC_001,
@INFA_USUA	TD_NU_009_004
 
AS	

CREATE TABLE #TWDOCU_CLIE_Q37_1 
 ( CO_EMPR      varchar(3) NULL,
   TI_CLIE	varchar(3) NULL ,
   CO_CLIE	varchar(20) NULL ,
   CO_TIPO_DOCU	varchar(3) NULL ,
   NU_DOCU_CLIE	varchar(20) NULL,
   FE_REPO	datetime NULL,
   FE_EMIS	datetime NULL,
   FE_VENC	datetime NULL,
   FE_DESP	datetime NULL,
   CO_TIPO_OPER varchar(3) NULL,
   CO_MONE	varchar(03) NULL,
   FA_TIPO_CAMB	numeric(9,4) NULL,
   IM_CXCB	numeric(16,4) DEFAULT 0,
   IM_PAGO	numeric(16,4) DEFAULT 0,
   IM_CXCB_CONV	numeric(16,4) DEFAULT 0,
   IM_PAGO_CONV	numeric(16,4) DEFAULT 0,
   IM_CONV	numeric(16,4) DEFAULT 0,
   IM_CONV_TOTA	numeric(16,4) DEFAULT 0,
   TI_DETA	varchar(1) NULL, 
   ST_FLAG	varchar(1) NULL)
  

CREATE TABLE #TWDOCU_CLIE_Q37_2 
 ( CO_EMPR      varchar(3) NULL,
   TI_CLIE	varchar(3) NULL, 
   CO_CLIE	varchar(20) NULL, 
   CO_MONE	varchar(3) NULL,
   IM_SAIN	numeric(16,4) DEFAULT 0,
   IM_SAIN_CONV	numeric(16,4) DEFAULT 0)


CREATE TABLE #TWDOCU_CLIE_Q37_3
 ( CO_EMPR      varchar(3) NULL,
   TI_CLIE	varchar(3) NULL ,
   CO_CLIE	varchar(20) NULL ,
   CO_TIPO_DOCU	varchar(3) NULL ,
   NU_DOCU_CLIE	varchar(20) NULL,
   FE_EMIS	datetime NULL,
   FE_VENC	datetime NULL,
   FE_DESP	datetime NULL,
   CO_TIPO_OPER varchar(3) NULL,
   CO_MONE	varchar(03) NULL,
   DE_MONE	varchar(20) NULL,
   SB_MONE	varchar(03) NULL,
   FA_TIPO_CAMB	numeric(9,4) NULL,
   IM_CXCB	numeric(16,4) DEFAULT 0,
   IM_PAGO	numeric(16,4) DEFAULT 0,
   IM_CXCB_CONV	numeric(16,4) DEFAULT 0,
   IM_PAGO_CONV	numeric(16,4) DEFAULT 0,
   IM_SAIN	numeric(16,4) DEFAULT 0,
   IM_SAIN_CONV	numeric(16,4) DEFAULT 0,
   IM_CONV	numeric(16,4) DEFAULT 0,
   IM_CONV_TOTA	numeric(16,4) DEFAULT 0,
   TI_DETA_CLIE char(1) NULL,
   TI_DETA_TICL char(1) NULL,
   TI_DETA_EMPR char(1) NULL,
   TI_DETA_GENE char(1) NULL)


DECLARE 

@VSCO_EMPR 	TD_VC_002,
@VSTI_CAMB	TD_VC_001,
@VSTI_ORIG	TD_VC_001,
@VSDE_ORDE	TD_VC_050,
@VSFE_INIC	TD_VC_010,
@VSFE_FINA	TD_VC_010,
@VSFE_CANC	TD_VC_010,
@VNIM_CXCB_CONV TD_NU_016_004,
@VNIM_PAGO_CONV TD_NU_016_004,
@VNFE_0001      TD_IN_001, 
@VSCA_WHER VARCHAR(600),
@VSST_ANTC	TD_VC_001,
@VDTIME		DATETIME,

@CSCO_EMPR  	TD_VC_002,
@CSCO_CLIE  	TD_VC_020,
@CSCO_TIPO_DOCU	TD_VC_003,
@CSNU_DOCU_CLIE	TD_VC_020,
@CSCO_MONE      TD_VC_003,
@CNIM_CXCB	TD_NU_016_004,
@CNIM_PAGO	TD_NU_016_004,
@CDFE_EMIS      TD_DT_001,
@CNFA_TIPO_CAMB TD_NU_009_004,
@CSTI_DETA      TD_VC_002,

@OSTI_DOCU_LETR	TD_VC_003,
@OSTI_DOCU_NCRE	TD_VC_003,
@OSTI_DOCU_OCMP	TD_VC_003,
@OSTI_DOCU_CHEQ TD_VC_003 


EXEC SP_TTDOCU_CNTB_Q01 4, @OSTI_DOCU_NCRE OUTPUT
EXEC SP_TTDOCU_CNTB_Q01 6, @OSTI_DOCU_LETR OUTPUT
  /* Devuelve el Tipo Documento CHEQUE */    
  Exec SP_TTDOCU_CNTB_Q01 29, @OSTI_DOCU_CHEQ Output 

SELECT @VSCA_WHER = REPLACE(@ISCA_WHER_0001+@ISCA_WHER_0002, "TMDOCC_GENE", "T4")


IF PATINDEX("%ANT%", @VSCA_WHER) > 0
    SELECT @VSST_ANTC = "S"
ELSE
    SELECT @VSST_ANTC = "N"

SELECT 	@VSFE_CANC = Convert(Varchar(10), @IDFE_CANC, 103),
	@VSFE_INIC = CONVERT(VARCHAR(10), @IDFE_INIC, 103),
	@VSFE_FINA = CONVERT(VARCHAR(10), @IDFE_FINA, 103)

-- TIPOS DE DETALLE(TI_DETA = "1" AL "7")
--1.- DOCS EMITIDOS EN ESOS MESES					
--2.- PAGOS BANCOS / CAJA
--3.- APLICACIONES DE NCR A DOCUMENTOS
--4.- CANJES
--5.- RENOVACIONES
--6.- APLICACION DE DOC. A ANTICIPOS
--8.- ANTICIPOS( SIN APLICACIONES, SALDO = IMPORTE DEPOSITO )
--7.- ANTICIPOS( CON APLICACION, SALDOS)

--SELECT @VDTIME = GETDATE()
--SELECT 'INICIO INSERTS', @VDTIME
--1.- DOCS EMITIDOS EN ESOS MESES
-- TMDOCU_CLIE
IF @ISFL_FECH = 'E'

  EXECUTE( "INSERT INTO #TWDOCU_CLIE_Q37_1 
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_REPO, FE_EMIS, FE_VENC, FE_DESP, CO_TIPO_OPER,
  CO_MONE, FA_TIPO_CAMB, IM_CXCB, TI_DETA)
  Select T4.CO_EMPR, T4.CO_TIPO_DOCU , T4.NU_DOCU_CLIE , T4.CO_CLIE, 
  T4.FE_EMIS, T4.FE_EMIS, T4.FE_VENC, 
  T4.FE_DESP, T4.CO_TIPO_OPER, T4.CO_MONE, T4.FA_TIPO_CAMB,  
  ( PatIndex(T2.ST_SIGN,'S') - PatIndex(T2.ST_SIGN,'N') ) * T4.IM_TOTA, '1'
  From  TMDOCC_GENE T4, TTDOCU_CNTB T2 " 
  + @VSCA_WHER + "
  And T4.TI_DOCU_ANTC ='N'
  And T4.FE_EMIS <= Convert(Datetime, '" + @VSFE_CANC + "', 103)
  And T4.CO_ESTA_DOCU != 'ANU'
  AND T4.CO_TIPO_DOCU = T2.TI_DOCU")
--SELECT 'FIN INSERT 1', GETDATE() - @VDTIME 
--SELECT @VDTIME = GETDATE()
   --EXECUTE SP_TTDOCU_CNTB_Q01 6, @OSTI_DOCU

IF @ISFL_FECH = 'V'

  EXECUTE("INSERT INTO #TWDOCU_CLIE_Q37_1 
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_REPO, FE_EMIS, FE_VENC, FE_DESP, CO_TIPO_OPER,
  CO_MONE, FA_TIPO_CAMB, IM_CXCB, TI_DETA)
  Select T4.CO_EMPR, T4.CO_TIPO_DOCU , T4.NU_DOCU_CLIE , T4.CO_CLIE, 
  T4.FE_EMIS, T4.FE_EMIS, T4.FE_VENC, T4.FE_DESP, T4.CO_TIPO_OPER, 
  T4.CO_MONE, T4.FA_TIPO_CAMB, T4.IM_TOTA, '1'
  From  TMDOCC_GENE T4, TTDOCU_CNTB T2 "
  + @VSCA_WHER + "
  And T4.TI_DOCU_ANTC ='N'
  And T4.FE_VENC <=  Convert(Datetime, '" + @VSFE_CANC + "', 103)
  And T4.CO_ESTA_DOCU != 'ANU'
  AND T4.CO_TIPO_DOCU = T2.TI_DOCU")


--2.- PAGOS BANCOS / CAJA
  EXECUTE("INSERT INTO #TWDOCU_CLIE_Q37_1 
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, CO_TIPO_OPER, FE_REPO, FE_EMIS, 
  CO_MONE, FA_TIPO_CAMB, IM_PAGO, TI_DETA)
  Select T1.CO_EMPR, T1.TI_DOCU_ENTI, T1.NU_DOCU_ENTI, T1.CO_ENTI, T2.CO_TIPO_OPER, T2.FE_OPER,
  T4.FE_EMIS, T4.CO_MONE, T2.FA_TIPO_CAMB,
  ( ( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') )*PatIndex(T1.TI_MOVI_BANC,'I')-
    ( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') )*PatIndex(T1.TI_MOVI_BANC,'E')
  )*( PatIndex(T1.ST_CIER_DOCU, 'N') * T1.IM_CONV_DOCU +
      PatIndex(T1.ST_CIER_DOCU, 'S') * T1.IM_SALD_DOCU ), '2'
  From  TDMOVI_BANC T1, TCMOVI_BANC T2, TMCNTA_BANC T3, TMDOCC_GENE T4, TTDOCU_CNTB T5 "
  + @VSCA_WHER + "
  And T4.TI_DOCU_ANTC ='N'
  And    T1.TI_ENTI = '" + @ISTI_AUXI_CLIE + "'   And    T2.FE_OPER  <= Convert(Datetime, '" + @VSFE_CANC + "', 103)
  And    T2.TI_SITU != 'ANU' 

  And    T1.CO_EMPR = T2.CO_EMPR  
  And    T1.CO_UNID_CONC = T2.CO_UNID_CONC
  And    T1.CO_BANC = T2.CO_BANC  
  And    T1.NU_CNTA_BANC  = T2.NU_CNTA_BANC  
  And    T1.AA_BNCO = T2.AA_BNCO
  And    T1.MM_BNCO = T2.MM_BNCO 
  And    T1.TI_MOVI_BANC  = T2.TI_MOVI_BANC 
  And    T1.NU_COMP_BANC = T2.NU_COMP_BANC

  And    T1.CO_EMPR = T3.CO_EMPR  
  And    T1.CO_BANC = T3.CO_BANC  
  And    T1.NU_CNTA_BANC  = T3.NU_CNTA_BANC

  AND	 T1.CO_EMPR = T4.CO_EMPR
  AND 	 T1.TI_DOCU_ENTI = T4.CO_TIPO_DOCU
  AND 	 T1.NU_DOCU_ENTI = T4.NU_DOCU_CLIE
  AND 	 T4.CO_TIPO_DOCU = T5.TI_DOCU")
--SELECT 'FIN INSERT 2-BANCO', GETDATE() - @VDTIME
--SELECT @VDTIME = GETDATE()
 
  EXECUTE("INSERT INTO #TWDOCU_CLIE_Q37_1 
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, CO_TIPO_OPER, FE_REPO, FE_EMIS,
  CO_MONE, FA_TIPO_CAMB, IM_PAGO, TI_DETA)
  Select T1.CO_EMPR, T1.TI_DOCU_ENTI, T1.NU_DOCU_ENTI, T1.CO_ENTI, T2.CO_TIPO_OPER, T2.FE_OPER,
  T4.FE_EMIS, T4.CO_MONE, T2.FA_TIPO_CAMB,
  ( ( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') )*PatIndex(T1.TI_MOVI,'I')-
    ( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') )*PatIndex(T1.TI_MOVI,'E')
  )*( PatIndex(T1.ST_CIER_DOCU, 'N') * T1.IM_CONV_DOCU +
      PatIndex(T1.ST_CIER_DOCU, 'S') * T1.IM_SALD_DOCU), '2'
  From   TDMOVI_CAJA T1, TCMOVI_CAJA T2, TMCAJA T3, TMDOCC_GENE T4, TTDOCU_CNTB T5 "
  + @VSCA_WHER + "
  And T4.TI_DOCU_ANTC ='N'
  And    T1.TI_ENTI = '" + @ISTI_AUXI_CLIE + "'
  And    T2.FE_OPER  <= Convert(Datetime, '" + @VSFE_CANC + "', 103)
  And    T2.TI_SITU != 'ANU' 

  And    T1.CO_EMPR = T2.CO_EMPR  
  And    T1.CO_CAJA = T2.CO_CAJA
  And    T1.AA_CAJA = T2.AA_CAJA
  And    T1.MM_CAJA = T2.MM_CAJA 
  And    T1.TI_MOVI = T2.TI_MOVI
  And    T1.NU_COMP_CAJA = T2.NU_COMP_CAJA

  And    T1.CO_EMPR = T3.CO_EMPR  
  And    T1.CO_CAJA = T3.CO_CAJA
 
  And	 T1.CO_EMPR = T4.CO_EMPR
  AND	 T1.TI_DOCU_ENTI = T4.CO_TIPO_DOCU
  And	 T1.NU_DOCU_ENTI = T4.NU_DOCU_CLIE

  AND 	 T4.CO_TIPO_DOCU = T5.TI_DOCU")

--SELECT 'FIN INSERT 2-CAJA', GETDATE() - @VDTIME
--SELECT @VDTIME = GETDATE()

-- PAGOS A CHEQUES DE LA TMDOCU_CLIE EN BANCOS, CON TDCHEQ_BANC
  EXECUTE("INSERT INTO #TWDOCU_CLIE_Q37_1 
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_REPO, FE_EMIS, FE_VENC,
  CO_MONE, FA_TIPO_CAMB, IM_PAGO, TI_DETA)
  Select T1.CO_EMPR, T4.CO_TIPO_DOCU, T1.NU_CHEQ, T1.CO_ENTI, T4.FE_EMIS, 
  T4.FE_EMIS, T4.FE_VENC, T4.CO_MONE, T2.FA_TIPO_CAMB,
  ( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') ) *T1.IM_CHEQ, '2'
  From  TDCHEQ_BANC T1, TCMOVI_BANC T2, TMCNTA_BANC T3, TMDOCC_GENE T4, TTDOCU_CNTB T5 "
  + @VSCA_WHER + "
  And T4.TI_DOCU_ANTC ='N'
  And    T1.TI_ENTI = '" + @ISTI_AUXI_CLIE + "'
  And    T2.FE_OPER  <= Convert(Datetime, '" + @VSFE_CANC + "', 103)
  And    T2.TI_SITU = 'ACT' 

  And    T1.CO_EMPR = T2.CO_EMPR  
  And    T1.CO_UNID_CONC = T2.CO_UNID_CONC
  And    T1.CO_BANC = T2.CO_BANC  
  And    T1.NU_CNTA_BANC  = T2.NU_CNTA_BANC  
  And    T1.AA_BNCO = T2.AA_BNCO
  And    T1.MM_BNCO = T2.MM_BNCO 
  And    T1.TI_MOVI_BANC  = T2.TI_MOVI_BANC 
  And    T1.NU_COMP_BANC = T2.NU_COMP_BANC

  And    T1.CO_EMPR = T3.CO_EMPR  
  And    T1.CO_BANC = T3.CO_BANC  
  And    T1.NU_CNTA_BANC  = T3.NU_CNTA_BANC

  AND	 T1.CO_EMPR = T4.CO_EMPR
  AND 	 @OSTI_DOCU_CHEQ = T4.CO_TIPO_DOCU
  AND 	 T1.NU_CHEQ = T4.NU_DOCU_CLIE
	
  AND 	 T4.CO_TIPO_DOCU = T5.TI_DOCU")
--SELECT 'FIN INSERT 2-CHEQUES', GETDATE() - @VDTIME
--SELECT @VDTIME = GETDATE()

--3.- APLICACIONES DE NOTA DE CREDITOS A DOCUMENTOS

-- DOCUMENTOS
  EXECUTE("INSERT INTO #TWDOCU_CLIE_Q37_1 
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, CO_TIPO_OPER, FE_REPO, 
  FE_EMIS, CO_MONE, FA_TIPO_CAMB, IM_PAGO, TI_DETA)
  SELECT T1.CO_EMPR, T4.CO_TIPO_DOCU, T4.NU_DOCU_CLIE, T1.CO_CLIE, T1.CO_TIPO_OPER, T1.FE_AMAR,
  T4.FE_EMIS, T4.CO_MONE, T1.FA_TIPO_CAMB, 
  (PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') ) * ISNULL(T2.IM_COBR_CDES, 0), '3'
  From   TCAMAR_NCCL T1 , TDAMAR_NCCL T2, TMDOCC_GENE T4, TTDOCU_CNTB T5 "
  + @VSCA_WHER + "
  And T4.TI_DOCU_ANTC ='N'
  AND	 T1.CO_EMPR =   T4.CO_EMPR
  AND	 T4.CO_TIPO_DOCU != '" + @OSTI_DOCU_NCRE + "'
  And    T1.FE_AMAR  <= Convert(Datetime, '" + @VSFE_CANC + "', 103)
  AND    T1.TI_SITU = 'ACT'
  AND    T2.CO_TIDO_DEST = T4.CO_TIPO_DOCU 
  AND    T2.NU_DOCU_DEST = T4.NU_DOCU_CLIE 
  AND    T1.CO_EMPR = T2.CO_EMPR  
  AND    T1.NU_AMAR = T2.NU_AMAR
  AND    T2.CO_EMPR = T4.CO_EMPR
  AND    T1.CO_CLIE = T4.CO_CLIE
  AND    T2.CO_TIDO_DEST = T4.CO_TIPO_DOCU
  AND    T2.NU_DOCU_DEST = T4.NU_DOCU_CLIE
  AND	 T4.CO_ESTA_DOCU != 'ANU' 
  AND	 T4.CO_TIPO_DOCU = T5.TI_DOCU")
--SELECT 'FIN INSERT 3', GETDATE() - @VDTIME
--SELECT @VDTIME = GETDATE()

-- NOTAS DE CREDITO APLICADAS 
  EXECUTE("INSERT INTO #TWDOCU_CLIE_Q37_1 
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, CO_TIPO_OPER, FE_REPO, 
  FE_EMIS, CO_MONE, FA_TIPO_CAMB, IM_PAGO, TI_DETA)
  SELECT T1.CO_EMPR, T4.CO_TIPO_DOCU, T4.NU_DOCU_CLIE, T1.CO_CLIE, T1.CO_TIPO_OPER, T1.FE_AMAR,
  T4.FE_EMIS, T4.CO_MONE, T1.FA_TIPO_CAMB,
  (PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') ) * ISNULL(T2.IM_COBR_CORI, 0), '3'
  From   TCAMAR_NCCL T1 , TDAMAR_NCCL T2, TMDOCC_GENE T4, TTDOCU_CNTB T5 "
  + @VSCA_WHER + "
  And T4.TI_DOCU_ANTC ='N'
  AND	 T1.CO_EMPR = T4.CO_EMPR
  AND	 T4.CO_TIPO_DOCU = '" + @OSTI_DOCU_NCRE + "'
  And    T1.FE_AMAR <= Convert(Datetime, '" + @VSFE_CANC + "', 103)
  AND    T1.TI_SITU = 'ACT'
  AND    T2.CO_TIDO_ORIG = T4.CO_TIPO_DOCU 
  AND    T2.NU_DOCU_ORIG = T4.NU_DOCU_CLIE 
  AND    T1.CO_EMPR = T2.CO_EMPR  
  AND    T1.NU_AMAR = T2.NU_AMAR
  AND    T2.CO_EMPR = T4.CO_EMPR
  AND    T1.CO_CLIE = T4.CO_CLIE
  AND    T2.CO_TIDO_ORIG = T4.CO_TIPO_DOCU
  AND    T2.NU_DOCU_ORIG = T4.NU_DOCU_CLIE
	  
  AND	 T4.CO_ESTA_DOCU != 'ANU' 
  AND 	 T4.CO_TIPO_DOCU = T5.TI_DOCU")
--SELECT 'FIN INSERT 3', GETDATE() - @VDTIME
--SELECT @VDTIME = GETDATE()

--4.- CANJES

-- SOLO MUESTRO LOS DOCUMENTOS QUE SE CANJEAN, NO LAS LETRAS GENERADAS
  EXECUTE("INSERT INTO #TWDOCU_CLIE_Q37_1 
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, CO_TIPO_OPER, FE_REPO, 
  FE_EMIS, CO_MONE, FA_TIPO_CAMB, IM_PAGO, TI_DETA)
  SELECT T1.CO_EMPR, T4.CO_TIPO_DOCU, T4.NU_DOCU_CLIE, T1.CO_CLIE, T1.CO_TIPO_OPER, T1.FE_CANJ, 
  T4.FE_EMIS, T4.CO_MONE, T1.FA_TIPO_CAMB, 
  ( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') ) * 
  ( PatIndex(T2.ST_CIER_DOCU,'N') * T2.IM_CANJ_MDOC + 
    PatIndex(T2.ST_CIER_DOCU,'S') * T2.IM_SALD_DOCU ), '4'
  From  TCCANJ_CLIE T1 , TDCANJ_CLIE T2, TMDOCC_GENE T4, TTDOCU_CNTB T5 " 
  + @VSCA_WHER + "
  And T4.TI_DOCU_ANTC ='N'
  And	T1.CO_EMPR =   T4.CO_EMPR
  And    T1.FE_CANJ  <= Convert(Datetime, '" + @VSFE_CANC + "', 103)
  AND    T1.TI_SITU != 'ANU'
  AND    T1.CO_EMPR = T2.CO_EMPR  
  AND    T1.CO_CLIE = T2.CO_CLIE
  AND    T1.NU_CANJ = T2.NU_CANJ
  AND    T2.CO_EMPR = T4.CO_EMPR
  AND    T2.CO_CLIE = T4.CO_CLIE
  AND    T2.CO_TIPO_DOCU = T4.CO_TIPO_DOCU 
  AND    T2.NU_DOCU_CLIE = T4.NU_DOCU_CLIE 

  AND    ( ( T4.FE_EMIS <= Convert(Datetime, '" + @VSFE_FINA + "', 103) AND '" + @ISFL_FECH + "' = 'E' )
    OR ( T4.FE_VENC <= Convert(Datetime, '" + @VSFE_FINA + "', 103) AND '" + @ISFL_FECH + "' = 'V' ) )

  AND    T4.CO_ESTA_DOCU != 'ANU' 
  AND    T4.CO_TIPO_DOCU = T5.TI_DOCU")
--SELECT 'FIN INSERT 4', GETDATE() - @VDTIME
--SELECT @VDTIME = GETDATE()

--5.- RENOVACIONES

-- SOLO MUESTRO LOS DOCUMENTOS QUE SE RENUEVAN, NO LAS LETRAS GENERADAS
  EXECUTE("INSERT INTO #TWDOCU_CLIE_Q37_1 
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, CO_TIPO_OPER, FE_REPO, 
  FE_EMIS, CO_MONE, FA_TIPO_CAMB, IM_PAGO, TI_DETA)
  SELECT T1.CO_EMPR, T4.CO_TIPO_DOCU, T4.NU_DOCU_CLIE, T1.CO_CLIE, T1.CO_TIPO_OPER, T1.FE_RENO,
  T4.FE_EMIS, T4.CO_MONE, T1.FA_TIPO_CAMB, 
  ( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') ) * 
  ( PatIndex(T2.ST_CIER_DOCU,'N') * T2.IM_RENO_MDOC + 

    PatIndex(T2.ST_CIER_DOCU,'S') * T2.IM_SALD_DOCU ), '5'
  From   TCRENO_CLIE T1 , TDRENO_CLIE T2, TMDOCC_GENE T4, TTDOCU_CNTB T5 "
  + @VSCA_WHER + "
  And T4.TI_DOCU_ANTC ='N'
  AND	 T1.CO_EMPR =   T4.CO_EMPR
  And    T1.FE_RENO  <= Convert(Datetime, '" + @VSFE_CANC + "', 103)
  AND    T1.TI_SITU != 'ANU'
  AND    T1.CO_EMPR = T2.CO_EMPR  
  AND    T1.CO_CLIE = T2.CO_CLIE
  AND    T1.NU_RENO = T2.NU_RENO
  AND    T2.CO_CLIE = T4.CO_CLIE
  AND    T2.CO_TIPO_DOCU = T4.CO_TIPO_DOCU 
  AND    T2.NU_DOCU_CLIE = T4.NU_DOCU_CLIE 
  AND    ( ( T4.FE_EMIS <= Convert(Datetime, '" + @VSFE_FINA + "', 103) AND '" + @ISFL_FECH + "' = 'E' )
    OR ( T4.FE_VENC <= Convert(Datetime, '" + @VSFE_FINA + "', 103) AND '" + @ISFL_FECH + "' = 'V' ) )
  AND	 T4.CO_ESTA_DOCU != 'ANU' 
  AND	 T4.CO_TIPO_DOCU = T5.TI_DOCU")
--SELECT 'FIN INSERT 5', GETDATE() - @VDTIME
--SELECT @VDTIME = GETDATE()

--6.- APLICACION DE DOCUMENTOS A ANTICIPOS

-- SOLO MUESTRO LOS DOCUMENTOS QUE SE APLICAN
  EXECUTE("INSERT INTO #TWDOCU_CLIE_Q37_1 
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, CO_TIPO_OPER, FE_REPO, 
  FE_EMIS, CO_MONE, FA_TIPO_CAMB, IM_PAGO, TI_DETA)
  SELECT T1.CO_EMPR, T4.CO_TIPO_DOCU, T4.NU_DOCU_CLIE, T4.CO_CLIE, T1.CO_TIPO_OPER, T1.FE_AMAR,
  T4.FE_EMIS, T4.CO_MONE, T1.FA_TIPO_CAMB,
  PatIndex(T2.ST_CIER_DOCU, 'N')*
  ( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') ) * T2.IM_CONV_DOCU +
    PatIndex(T2.ST_CIER_DOCU, 'S')*
  ( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') ) * T2.IM_SALD_DOCU, '6'
  From   TCAMAR_ANTC T1 , TDAMAR_ANTC T2, TMDOCC_GENE T4, TTDOCU_CNTB T5 "
  + @VSCA_WHER + "
  And T4.TI_DOCU_ANTC ='N'
  And    T2.FE_AMAR_DETA  <= Convert(Datetime, '" + @VSFE_CANC + "', 103)
  AND    T1.TI_SITU != 'ANU'
  AND    T1.CO_EMPR = T2.CO_EMPR  
  AND    T1.NU_AMAR = T2.NU_AMAR
  AND	 T2.CO_EMPR =   T4.CO_EMPR
  AND    T2.TI_ENTI = '" + @ISTI_AUXI_CLIE + "' 
  AND    T2.TI_DOCU_ENTI = T4.CO_TIPO_DOCU 
  AND    T2.NU_DOCU_ENTI = T4.NU_DOCU_CLIE 

  AND    ( ( T4.FE_EMIS <= Convert(Datetime, '" + @VSFE_FINA + "', 103) AND '" + @ISFL_FECH + "' = 'E' )
    OR ( T4.FE_VENC <= Convert(Datetime, '" + @VSFE_FINA + "', 103) AND '" + @ISFL_FECH + "' = 'V' ) )

  AND	 T4.CO_ESTA_DOCU != 'ANU' 
  AND	 T4.CO_TIPO_DOCU = T5.TI_DOCU")
--SELECT 'FIN INSERT 6', GETDATE() - @VDTIME
--SELECT @VDTIME = GETDATE()

--7.- ANTICIPOS - PAGOS BANCOS 

  EXECUTE("INSERT INTO #TWDOCU_CLIE_Q37_1 
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, CO_TIPO_OPER, FE_REPO, 
  FE_EMIS, CO_MONE, FA_TIPO_CAMB, IM_PAGO, TI_DETA)
  Select T4.CO_EMPR, T4.CO_TIPO_DOCU, T4.NU_DOCU_CLIE, T4.CO_CLIE,
  T4.CO_TIPO_OPER, T4.FE_EMIS, T4.FE_EMIS, T4.CO_MONE, T4.FA_TIPO_CAMB, 
  T4.IM_TOTA, '7'
  From  TMDOCC_GENE T4 "
  + @VSCA_WHER + "
  And 	 T4.TI_DOCU_ANTC = 'B'
  And    T4.FE_EMIS  <= Convert(Datetime, '" + @VSFE_CANC + "', 103)
  And    T4.CO_ESTA_DOCU = 'ACT' ")
--SELECT 'FIN INSERT 7', GETDATE() - @VDTIME
--SELECT @VDTIME = GETDATE()

--8.- ANTICIPOS - PAGOS BANCOS 
-- ANTICIPO CON APLICACION 
  EXECUTE("INSERT INTO #TWDOCU_CLIE_Q37_1 
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, CO_TIPO_OPER, FE_REPO,
  FE_EMIS, CO_MONE, FA_TIPO_CAMB, IM_CXCB, TI_DETA)
  Select T4.CO_EMPR, T4.CO_TIPO_DOCU, T4.NU_DOCU_CLIE, T4.CO_CLIE,
  MAX(T4.CO_TIPO_OPER), T7.FE_AMAR_DETA, MAX(T4.FE_EMIS), T4.CO_MONE, MAX(T4.FA_TIPO_CAMB), 
  ISNULL(SUM(T7.IM_DETA_NEGA),0), '8'
  From  TMDOCC_GENE T4, TCAMAR_ANTC T6, TDAMAR_ANTC T7 "
  + @VSCA_WHER + "
  And 	 T4.TI_DOCU_ANTC = 'B'
  And    T4.CO_ESTA_DOCU = 'ACT' 
  And    T7.FE_AMAR_DETA  <= Convert(Datetime, '" + @VSFE_CANC + "', 103)

  AND	 T6.TI_SITU = 'ACT'
  AND	 T4.CO_EMPR = T6.CO_EMPR
  AND	 T4.CO_CLAV = T6.CO_BANC+T6.NU_CNTA_BANC+T6.AA_BNCO+T6.MM_BNCO+T6.NU_COMP_BANC

  AND	 T6.CO_EMPR = T7.CO_EMPR 
  AND 	 T6.NU_AMAR = T7.NU_AMAR

  GROUP BY T4.CO_EMPR, T4.CO_TIPO_DOCU, T4.NU_DOCU_CLIE, T4.CO_CLIE,
	   T4.CO_MONE, T4.CO_CLAV, T7.FE_AMAR_DETA")
--SELECT 'FIN INSERT 8', GETDATE() - @VDTIME
--SELECT @VDTIME = GETDATE()

/** ANTICIPOS CAJA **/ 
  EXECUTE("INSERT INTO #TWDOCU_CLIE_Q37_1 
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_REPO, FE_EMIS, FE_VENC, 
  CO_MONE, FA_TIPO_CAMB, IM_PAGO, TI_DETA)
  Select T4.CO_EMPR, T4.CO_TIPO_DOCU, T4.NU_DOCU_CLIE, 
  T4.CO_CLIE, T4.FE_EMIS, T4.FE_EMIS, T4.FE_EMIS, 
  T4.CO_MONE, T4.FA_TIPO_CAMB, T4.IM_TOTA, '7'
  From  TMDOCC_GENE T4 "
  + @VSCA_WHER + "
  AND 	T4.TI_DOCU_ANTC = 'C'
  And   T4.FE_EMIS  <= Convert(Datetime, '" + @VSFE_CANC + "', 103)
  And   T4.CO_ESTA_DOCU = 'ACT' ")
--SELECT 'FIN INSERT 7-CAJA', GETDATE() - @VDTIME
--SELECT @VDTIME = GETDATE()

/** ANTICIPOS CAJA - APLICACION**/ 
  EXECUTE("INSERT INTO #TWDOCU_CLIE_Q37_1 
  (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_REPO, FE_EMIS, FE_VENC, 
  CO_MONE, FA_TIPO_CAMB, IM_CXCB, TI_DETA)
  Select T4.CO_EMPR, T4.CO_TIPO_DOCU, T4.NU_DOCU_CLIE, T4.CO_CLIE, 
  T7.FE_AMAR_DETA, MAX(T4.FE_EMIS), MAX(T4.FE_EMIS), T4.CO_MONE,
  MAX(T4.FA_TIPO_CAMB), ISNULL(SUM(T7.IM_DETA_NEGA), 0), '8'
  From  TMDOCC_GENE T4, TCAMAR_ANTC T6, TDAMAR_ANTC T7 "
  + @VSCA_WHER + "
  AND 	 T4.TI_DOCU_ANTC = 'C'
  And    T4.CO_ESTA_DOCU = 'ACT' 
  And    T7.FE_AMAR_DETA <= Convert(Datetime, '" + @VSFE_CANC + "', 103)

  AND	 T6.TI_SITU = 'ACT'
  AND	 T4.CO_EMPR = T6.CO_EMPR 
  And    T4.CO_CLAV = T6.CO_CAJA+T6.AA_CAJA+T6.MM_CAJA+T6.NU_COMP_CAJA

  AND	 T6.CO_EMPR = T7.CO_EMPR 
  AND 	 T6.NU_AMAR = T7.NU_AMAR

  GROUP BY T4.CO_EMPR, T4.CO_TIPO_DOCU, T4.NU_DOCU_CLIE, T4.CO_CLIE,
	   T4.CO_MONE, T4.CO_CLAV, T7.FE_AMAR_DETA")
--SELECT 'FIN INSERT 8 -CAJA', GETDATE() - @VDTIME
--SELECT @VDTIME = GETDATE()

-------------------------------------------------------------------------------
-----------   AGRUPO POR DOCUMENTO Y LO INSERTO EN LA TABLA TEMPORAL   ---------
--------------------------------------------------------------------------------

	INSERT INTO #TWDOCU_CLIE_Q37_1 
	(CO_EMPR, TI_CLIE, CO_CLIE, CO_TIPO_DOCU, NU_DOCU_CLIE, FE_EMIS, FE_VENC, FE_DESP, CO_TIPO_OPER, CO_MONE,
   	FA_TIPO_CAMB, IM_CXCB, IM_PAGO, ST_FLAG)
	SELECT T1.CO_EMPR, MAX(ISNULL(T3.CO_TIPO_CLIE, '')), T1.CO_CLIE, T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE, MIN(T1.FE_EMIS), 
	MAX(T1.FE_VENC), MAX(T1.FE_DESP), MAX(T1.CO_TIPO_OPER), MAX(T1.CO_MONE), MAX(T1.FA_TIPO_CAMB), 
	SUM(T1.IM_CXCB), SUM(T1.IM_PAGO), 'S'
   	FROM 	#TWDOCU_CLIE_Q37_1 T1
    INNER JOIN TMAUXI_EMPR T2
    ON T2.TI_AUXI_EMPR = @ISTI_AUXI_CLIE
	AND T2.CO_EMPR = T1.CO_EMPR
	AND T2.CO_AUXI_EMPR = T1.CO_CLIE
    LEFT OUTER JOIN TTTIPO_CLIE T3 
	ON T2.TI_CLIE = T3.CO_TIPO_CLIE	
	WHERE T1.FE_REPO >= @IDFE_INIC
	GROUP BY T1.CO_EMPR, T1.CO_CLIE, T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE

IF @ISTI_SALD = 'C'
	DELETE	#TWDOCU_CLIE_Q37_1
   	WHERE 	ST_FLAG = 'S' 	
	AND 	ROUND(IM_CXCB, 2) = ROUND(IM_PAGO, 2)

IF @ISTI_SALD = 'S'
    BEGIN
	DELETE	#TWDOCU_CLIE_Q37_1
   	WHERE 	ST_FLAG = 'S' 	
	AND	ROUND(IM_CXCB, 2) != ROUND(IM_PAGO, 2)

	UPDATE #TWDOCU_CLIE_Q37_1
	SET IM_CONV = 0
   	WHERE 	ST_FLAG = 'S' 	
    END

IF @ISTI_SALD = 'T'
	UPDATE #TWDOCU_CLIE_Q37_1
	SET IM_CONV = 0
   	WHERE 	ST_FLAG = 'S' 	
	AND 	ROUND(IM_CXCB, 2) = ROUND(IM_PAGO, 2)

------------------------------------------------------------------
-----	CONVIERTO LOS IMPORTES A LA MONEDA DE PRESENTACION
------------------------------------------------------------------

IF @ISST_USUA = 'S'
  UPDATE #TWDOCU_CLIE_Q37_1
  SET IM_CXCB_CONV = (SELECT OFITESO.dbo.FN_TCFACT_CAMB_Q02(CO_MONE, @ISCO_MONE_PRES, FE_REPO, 
			IM_CXCB, T2.TI_CAMB, T2.TI_ORIG, 'S', T2.CO_MONE_DEFA, T2.CO_MONE_EXTR, @INFA_USUA)),
      IM_PAGO_CONV = (SELECT OFITESO.dbo.FN_TCFACT_CAMB_Q02(CO_MONE, @ISCO_MONE_PRES, FE_REPO, 
			IM_PAGO, T2.TI_CAMB, T2.TI_ORIG, 'S', T2.CO_MONE_DEFA, T2.CO_MONE_EXTR, @INFA_USUA))
  FROM #TWDOCU_CLIE_Q37_1 T1, TMPARA_TESO T2
  WHERE T1.CO_EMPR = T2.CO_EMPR
  AND T1.ST_FLAG = 'S'
  AND ROUND(T1.IM_CXCB, 2) != ROUND(T1.IM_PAGO, 2)
ELSE
  UPDATE #TWDOCU_CLIE_Q37_1
  SET IM_CXCB_CONV = (SELECT OFITESO.dbo.FN_TCFACT_CAMB_Q02(CO_MONE, @ISCO_MONE_PRES, FE_REPO, 
			IM_CXCB, T2.TI_CAMB, T2.TI_ORIG, 'S', T2.CO_MONE_DEFA, T2.CO_MONE_EXTR, T1.FA_TIPO_CAMB)),
      IM_PAGO_CONV = (SELECT OFITESO.dbo.FN_TCFACT_CAMB_Q02(CO_MONE, @ISCO_MONE_PRES, FE_REPO, 
			IM_PAGO, T2.TI_CAMB, T2.TI_ORIG, 'S', T2.CO_MONE_DEFA, T2.CO_MONE_EXTR, T1.FA_TIPO_CAMB))
  FROM #TWDOCU_CLIE_Q37_1 T1, TMPARA_TESO T2
  WHERE T1.CO_EMPR = T2.CO_EMPR
  AND T1.ST_FLAG = 'S'
  AND ROUND(T1.IM_CXCB, 2) != ROUND(T1.IM_PAGO, 2)

UPDATE #TWDOCU_CLIE_Q37_1
SET IM_CONV = IM_CXCB_CONV - IM_PAGO_CONV
WHERE ST_FLAG = 'S'
AND ROUND(IM_CXCB, 2) != ROUND(IM_PAGO, 2)

-- SALDOS INICIALES
	INSERT INTO #TWDOCU_CLIE_Q37_2(CO_EMPR, TI_CLIE, CO_CLIE, CO_MONE, IM_SAIN, IM_SAIN_CONV) 
	SELECT T1.CO_EMPR, T2.TI_CLIE, T1.CO_CLIE, T1.CO_MONE, 
	SUM(ISNULL(T1.IM_CXCB, 0)) - SUM(ISNULL(T1.IM_PAGO, 0)), SUM(ISNULL(T1.IM_CXCB_CONV - T1.IM_PAGO_CONV, 0))
	FROM #TWDOCU_CLIE_Q37_1 T1, TMAUXI_EMPR T2
	WHERE T1.FE_REPO < @IDFE_INIC
	AND T1.ST_FLAG IS NULL
	AND T1.CO_EMPR = T2.CO_EMPR
	AND T2.TI_AUXI_EMPR = @ISTI_AUXI_CLIE
	AND T1.CO_CLIE = T2.CO_AUXI_EMPR
	GROUP BY T1.CO_EMPR, T2.TI_CLIE, T1.CO_CLIE, T1.CO_MONE


DELETE #TWDOCU_CLIE_Q37_1
WHERE ST_FLAG IS NULL

--SELECT * FROM #TWDOCU_CLIE_Q37_2
--SELECT * FROM #TWDOCU_CLIE_Q37_1

--DETALLADO
IF @ISTI_REPO = 'D' 
BEGIN
	
	IF @ISDE_ORDE = '1'
		SELECT @VSDE_ORDE = ' ORDER BY 1, 30, 3, 29, 5, 28, 31, 7, 8 '
	IF @ISDE_ORDE = '2'
		SELECT @VSDE_ORDE = ' ORDER BY 1, 30, 3, 29, 5, 28, 7 , 8 '
	IF @ISDE_ORDE = '3'
		SELECT @VSDE_ORDE = ' ORDER BY 1, 30, 3, 29, 6, 28, 7, 8 '

--SELECT 'INSERT TABLA PARA REPORTE', GETDATE() - @VDTIME
--SELECT @VDTIME = GETDATE()

	INSERT INTO #TWDOCU_CLIE_Q37_3 ( CO_EMPR, TI_CLIE, CO_CLIE, CO_TIPO_DOCU,
	NU_DOCU_CLIE, FE_EMIS, FE_VENC, CO_MONE, CO_TIPO_OPER, IM_SAIN, IM_SAIN_CONV,
	IM_CXCB, IM_PAGO, FA_TIPO_CAMB, FE_DESP, IM_CONV, IM_CONV_TOTA, TI_DETA_CLIE, 
	TI_DETA_TICL, TI_DETA_EMPR)
	SELECT  T1.CO_EMPR, T1.TI_CLIE, T1.CO_CLIE, T1.CO_TIPO_DOCU, 
	T1.NU_DOCU_CLIE, T1.FE_EMIS, T1.FE_VENC, T1.CO_MONE, T1.CO_TIPO_OPER, 0, 0, 
	ISNULL( T1.IM_CXCB, 0 ), ISNULL( T1.IM_PAGO, 0 ), ISNULL( T1.FA_TIPO_CAMB, 0 ), 
	T1.FE_DESP, T1.IM_CONV, 0, '1', '1', '1'
	FROM #TWDOCU_CLIE_Q37_1 T1 
	UNION ALL
	SELECT	T1.CO_EMPR, T1.TI_CLIE, T1.CO_CLIE, '',
	'', MAX(T1.FE_EMIS), NULL, MAX(T2.CO_MONE), '',	ISNULL( MAX(T3.IM_SAIN), 0 ), ISNULL( MAX(T3.IM_SAIN_CONV), 0 
), 
	SUM(ISNULL(T1.IM_CXCB, 0)), SUM(ISNULL(T1.IM_PAGO, 0)), 0, NULL, SUM(ISNULL(T1.IM_CONV, 0)), 
	SUM(ISNULL(T1.IM_CONV, 0)) + ISNULL( MAX(T3.IM_SAIN_CONV), 0 ), '2', '1', '1'
	FROM 	#TWDOCU_CLIE_Q37_1 T1
    LEFT OUTER JOIN #TWDOCU_CLIE_Q37_2 T3
    ON  T1.CO_EMPR = T3.CO_EMPR
	And T1.CO_CLIE = T3.CO_CLIE
	And T1.CO_MONE = T3.CO_MONE 
    INNER JOIN TTMONE T2
	ON T1.CO_MONE = T2.CO_MONE 
	GROUP BY T1.CO_EMPR,  T1.TI_CLIE, T1.CO_CLIE, T1.CO_MONE
	UNION ALL
	SELECT	T1.CO_EMPR, T1.TI_CLIE, T1.CO_CLIE, '',
	'', NULL, NULL, T2.CO_MONE, '', ISNULL(T1.IM_SAIN, 0), ISNULL(T1.IM_SAIN_CONV, 0), 
	0, 0, 0, NULL, 0, ISNULL(T1.IM_SAIN_CONV, 0), '2', '1', '1'
	FROM 	#TWDOCU_CLIE_Q37_2 T1, TTMONE T2
	WHERE 	T1.CO_MONE = T2.CO_MONE 
	AND NOT EXISTS (SELECT 	T3.CO_EMPR
			FROM 	#TWDOCU_CLIE_Q37_1 T3
			WHERE 	T3.ST_FLAG = 'S'
			AND	T1.CO_EMPR = T3.CO_EMPR
			AND 	T1.CO_CLIE = T3.CO_CLIE
			AND 	T1.CO_MONE = T3.CO_MONE )
	UNION ALL
	SELECT	T1.CO_EMPR, T1.TI_CLIE, T1.CO_CLIE, '',  
	'', NULL, NULL, '', '', 0, ISNULL(MAX(T3.IM_SAIN_CONV), 0),
	SUM(ISNULL( T1.IM_CXCB_CONV, 0 )), SUM(ISNULL( T1.IM_PAGO_CONV, 0 )), 
	0, NULL, SUM(ISNULL( T1.IM_CONV, 0 )), 
	ISNULL(MAX(T3.IM_SAIN_CONV), 0) + SUM(ISNULL( T1.IM_CXCB_CONV, 0 )) - 
	SUM(ISNULL( T1.IM_PAGO_CONV, 0 )), '3' , '1' , '1'
	FROM 	#TWDOCU_CLIE_Q37_1 T1
    INNER JOIN (SELECT CO_EMPR, TI_CLIE, CO_CLIE, 
				SUM(ISNULL(IM_SAIN_CONV, 0)) IM_SAIN_CONV 
				FROM #TWDOCU_CLIE_Q37_2 
				GROUP BY CO_EMPR, TI_CLIE, CO_CLIE) T3
    ON  T1.CO_EMPR = T3.CO_EMPR
	AND T1.TI_CLIE = T3.TI_CLIE
	AND T1.CO_CLIE = T3.CO_CLIE
	GROUP BY T1.CO_EMPR, T1.TI_CLIE, T1.CO_CLIE
	UNION ALL
	SELECT	T1.CO_EMPR, T1.TI_CLIE, T1.CO_CLIE, '',  
	'', NULL, NULL, '', '', 0, SUM(ISNULL(T1.IM_SAIN_CONV, 0)), 
	0, 0, 0, NULL, 0, SUM(ISNULL(T1.IM_SAIN_CONV, 0)), '3', '1', '1'
	FROM 	#TWDOCU_CLIE_Q37_2 T1
	WHERE 	NOT EXISTS (SELECT 	T3.CO_EMPR
			FROM 	#TWDOCU_CLIE_Q37_1 T3
			WHERE 	T1.CO_EMPR = T3.CO_EMPR
			AND 	T1.CO_CLIE = T3.CO_CLIE )
	GROUP BY T1.CO_EMPR, T1.TI_CLIE, T1.CO_CLIE
	UNION ALL
	SELECT	T1.CO_EMPR, T1.TI_CLIE, (SELECT MAX(CO_CLIE)
						FROM #TWDOCU_CLIE_Q37_1), '', 
	'', MAX(T1.FE_EMIS), NULL, MAX(T2.CO_MONE), '', ISNULL(MAX(T3.IM_SAIN), 0), ISNULL(MAX(T3.IM_SAIN_CONV), 0), 
	SUM(ISNULL(T1.IM_CXCB, 0)), SUM(ISNULL(T1.IM_PAGO, 0)), 0, NULL, SUM(ISNULL(T1.IM_CONV, 0)), 
	ISNULL(MAX(T3.IM_SAIN_CONV), 0) + SUM(ISNULL(T1.IM_CONV, 0)), '2', '2', '1'
	FROM 	#TWDOCU_CLIE_Q37_1 T1
    INNER JOIN TTMONE T2
    ON T1.CO_MONE = T2.CO_MONE
    LEFT OUTER JOIN (SELECT CO_EMPR, TI_CLIE, CO_MONE,
							  SUM(ISNULL(IM_SAIN, 0)) IM_SAIN, 
							  SUM(ISNULL(IM_SAIN_CONV, 0)) IM_SAIN_CONV
						FROM #TWDOCU_CLIE_Q37_2 
						GROUP BY CO_EMPR, TI_CLIE, CO_MONE)T3
    ON  T1.CO_EMPR = T3.CO_EMPR
	AND T1.TI_CLIE = T3.TI_CLIE
	AND T1.CO_MONE = T3.CO_MONE	
	GROUP BY T1.CO_EMPR,  T1.TI_CLIE, T1.CO_MONE
	UNION ALL
	SELECT	T1.CO_EMPR, T1.TI_CLIE, (SELECT MAX(CO_CLIE)
						FROM #TWDOCU_CLIE_Q37_1), '', 
	'', NULL, NULL, MAX(T2.CO_MONE), '', SUM(ISNULL(T1.IM_SAIN, 0)), SUM(ISNULL(T1.IM_SAIN_CONV, 0)), 
	0, 0, 0, NULL, 0, SUM(ISNULL(T1.IM_SAIN_CONV, 0)), '2', '2', '1'
	FROM 	#TWDOCU_CLIE_Q37_2 T1, TTMONE T2
	WHERE 	T1.CO_MONE = T2.CO_MONE 
	AND NOT EXISTS (SELECT 	T3.CO_EMPR
			FROM 	#TWDOCU_CLIE_Q37_1 T3
			WHERE 	T3.ST_FLAG = 'S'
			AND	T1.CO_EMPR = T3.CO_EMPR
			AND 	T1.TI_CLIE = T3.TI_CLIE
			AND 	T1.CO_MONE = T3.CO_MONE )
	GROUP BY T1.CO_EMPR,  T1.TI_CLIE, T1.CO_MONE
	UNION ALL
	SELECT	T1.CO_EMPR, T1.TI_CLIE, MAX(T1.CO_CLIE), '', 
	'', NULL, NULL, '', '', 0, ISNULL(MAX(T3.IM_SAIN_CONV), 0), 
	SUM(ISNULL( T1.IM_CXCB_CONV, 0 )), SUM(ISNULL( T1.IM_PAGO_CONV, 0 )), 
	0, NULL, SUM(ISNULL( T1.IM_CONV, 0 )), 
	ISNULL(MAX(T3.IM_SAIN_CONV), 0) + SUM(ISNULL( T1.IM_CXCB_CONV, 0 )) - 
	SUM(ISNULL( T1.IM_PAGO_CONV, 0 )), '3' , '3' , '1'
	FROM 	#TWDOCU_CLIE_Q37_1 T1
    LEFT OUTER JOIN (SELECT CO_EMPR, TI_CLIE, 
						SUM(ISNULL(IM_SAIN_CONV, 0)) IM_SAIN_CONV 
					FROM #TWDOCU_CLIE_Q37_2 
					GROUP BY CO_EMPR, TI_CLIE) T3
    ON  T1.CO_EMPR = T3.CO_EMPR
	AND T1.TI_CLIE = T3.TI_CLIE
	GROUP BY T1.CO_EMPR, T1.TI_CLIE
	UNION ALL
	SELECT	T1.CO_EMPR, T1.TI_CLIE, MAX(T1.CO_CLIE), '',
	'', NULL, NULL, '', '', 0, SUM(ISNULL(T1.IM_SAIN_CONV, 0)), 
	0, 0, 0, NULL, 0, SUM(ISNULL(T1.IM_SAIN_CONV, 0)), '3', '3', '1'
	FROM 	#TWDOCU_CLIE_Q37_2 T1
	WHERE 	NOT EXISTS (SELECT 	T3.CO_EMPR
			FROM 	#TWDOCU_CLIE_Q37_1 T3
			WHERE 	T1.CO_EMPR = T3.CO_EMPR
			AND 	T1.TI_CLIE = T3.TI_CLIE )
	GROUP BY T1.CO_EMPR, T1.TI_CLIE
	UNION ALL
	SELECT	T1.CO_EMPR, MAX(T1.TI_CLIE), (SELECT MAX(CO_CLIE)
						FROM #TWDOCU_CLIE_Q37_1),'', 
	'', MAX(T1.FE_EMIS), NULL, MAX(T2.CO_MONE), '',	ISNULL(MAX(T3.IM_SAIN), 0), ISNULL(MAX(T3.IM_SAIN_CONV), 0), 
	SUM(ISNULL(T1.IM_CXCB, 0)), SUM(ISNULL(T1.IM_PAGO, 0)), 0, NULL, SUM(ISNULL(T1.IM_CONV, 0)), 
	ISNULL(MAX(T3.IM_SAIN_CONV), 0) + SUM(ISNULL(T1.IM_CONV, 0)), '2', '2', '2'
	FROM 	#TWDOCU_CLIE_Q37_1 T1
    INNER JOIN TTMONE T2
    ON T1.CO_MONE = T2.CO_MONE 
    LEFT OUTER JOIN (SELECT CO_EMPR, CO_MONE,
						  SUM(ISNULL(IM_SAIN, 0)) IM_SAIN,
						  SUM(ISNULL(IM_SAIN_CONV, 0)) IM_SAIN_CONV  
					FROM #TWDOCU_CLIE_Q37_2 
					GROUP BY CO_EMPR, CO_MONE)T3
    ON  T1.CO_EMPR = T3.CO_EMPR
	AND T1.CO_MONE = T3.CO_MONE 
	WHERE 	T1.ST_FLAG = 'S'	
	GROUP BY T1.CO_EMPR,  T1.CO_MONE 
	UNION ALL
	SELECT	T1.CO_EMPR, MAX(T1.TI_CLIE), (SELECT MAX(CO_CLIE)
							FROM #TWDOCU_CLIE_Q37_1), '', 
	'', NULL, NULL, MAX(T2.CO_MONE), '', SUM(ISNULL(T1.IM_SAIN, 0)), SUM(ISNULL(T1.IM_SAIN_CONV, 0)), 
	0, 0, 0, NULL, 0, SUM(ISNULL(T1.IM_SAIN_CONV, 0)), '2', '2', '2'
	FROM 	#TWDOCU_CLIE_Q37_2 T1, TTMONE T2
	WHERE 	T1.CO_MONE = T2.CO_MONE 
	AND NOT EXISTS (SELECT 	T3.CO_EMPR
			FROM 	#TWDOCU_CLIE_Q37_1 T3
			WHERE 	T3.ST_FLAG = 'S'
			AND	T1.CO_EMPR = T3.CO_EMPR
			AND 	T1.CO_MONE = T3.CO_MONE )
	GROUP BY T1.CO_EMPR,  T1.CO_MONE
	UNION ALL
	SELECT	T1.CO_EMPR, MAX(T1.TI_CLIE), MAX(T1.CO_CLIE), '',  
	'', NULL, NULL, '', '', 0, ISNULL(MAX(T3.IM_SAIN_CONV), 0),
	SUM(ISNULL( T1.IM_CXCB_CONV, 0 )), SUM(ISNULL( T1.IM_PAGO_CONV, 0 )), 0, NULL, 
	SUM(ISNULL( T1.IM_CONV, 0 )), ISNULL(MAX(T3.IM_SAIN_CONV), 0) + SUM(ISNULL( T1.IM_CXCB_CONV, 0 )) - 
	SUM(ISNULL( T1.IM_PAGO_CONV, 0 )), '3' , '3' , '3'
	FROM 	#TWDOCU_CLIE_Q37_1 T1
    LEFT OUTER JOIN (SELECT CO_EMPR,
						SUM(ISNULL(IM_SAIN_CONV, 0)) IM_SAIN_CONV 
					FROM #TWDOCU_CLIE_Q37_2 
					GROUP BY CO_EMPR) T3
    ON T1.CO_EMPR = T3.CO_EMPR
	GROUP BY T1.CO_EMPR
	UNION ALL
	SELECT	T1.CO_EMPR, MAX(T1.TI_CLIE), MAX(T1.CO_CLIE), '', 
	'', NULL, NULL, '', '', 0, SUM(ISNULL(T1.IM_SAIN_CONV, 0)), 
	0, 0, 0, NULL, 0, SUM(ISNULL(T1.IM_SAIN_CONV, 0)), '3', '3', '3'
	FROM 	#TWDOCU_CLIE_Q37_2 T1
	WHERE 	NOT EXISTS (SELECT 	T3.CO_EMPR
			FROM 	#TWDOCU_CLIE_Q37_1 T3
			WHERE 	T1.CO_EMPR = T3.CO_EMPR )
	GROUP BY T1.CO_EMPR

--SELECT 'FIN INSERT PARA REPORTE', GETDATE() - @VDTIME
--SELECT @VDTIME = GETDATE()

	EXECUTE(
	'SELECT	T1.CO_EMPR,  T2.DE_NOMB, T1.TI_CLIE, T4.DE_TIPO_CLIE, T1.CO_CLIE, T4.NO_AUXI,  
		T1.CO_TIPO_DOCU, T1.NU_DOCU_CLIE, CONVERT(VARCHAR(10),T1.FE_EMIS, 103), CONVERT(VARCHAR(10),T1.FE_VENC, 103), 
		T1.CO_MONE, T1.CO_TIPO_OPER, T1.IM_SAIN, T1.IM_SAIN_CONV, T1.IM_CXCB, T1.IM_PAGO, 
		T1.FA_TIPO_CAMB, T4.NO_DIRE, T4.CO_UBIC_GEOG, T4.NU_TLFN, T4.NU_FAXE, T4.IM_LIMI_CRED, 
		T4.CO_MONE_CRED, T1.FE_DESP, T1.IM_CONV, T1.IM_CONV_TOTA, T5.DE_MONE, T1.TI_DETA_CLIE, 
		T1.TI_DETA_TICL, T1.TI_DETA_EMPR, T1.FE_EMIS,
		Datediff(Day, T1.FE_VENC, @IDFE_CANC),
		T6.NU_FACT, T7.NU_CANJ, T7.CO_BANC, T7.NU_UNIC_BANC
	FROM	#TWDOCU_CLIE_Q37_3 T1
    INNER JOIN TMEMPR T2
    ON T1.CO_EMPR = T2.CO_EMPR
    INNER JOIN TMAUXI_EMPR T4
    ON  T4.TI_AUXI_EMPR = "' + @ISTI_AUXI_CLIE + '"	
	AND T1.CO_EMPR = T4.CO_EMPR
	AND T1.CO_CLIE = T4.CO_AUXI_EMPR
    LEFT OUTER JOIN TTMONE T5
    ON T1.CO_MONE = T5.CO_MONE
    LEFT OUTER JOIN TTFACT_INTE T6
    ON  T1.CO_EMPR = T6.CO_EMPR
	And	T1.CO_MONE = T6.CO_MONE
    And Datediff(Day, T1.FE_VENC, @IDFE_CANC) = T6.NU_DIAS
    LEFT OUTER JOIN TMLETR_CLIE T7
    ON  T1.CO_EMPR = T7.CO_EMPR
	And T1.NU_DOCU_CLIE = T7.NU_LETR_CLIE
	And T4.CO_MONE_CRED = T7.CO_MONE
	WHERE 	(T1.IM_SAIN != 0
	OR 	T1.IM_SAIN_CONV != 0
	OR 	T1.IM_CXCB != 0 
	OR 	T1.IM_PAGO != 0)' + @VSDE_ORDE)

END

IF @ISTI_REPO = 'R' 
BEGIN

	IF @ISDE_ORDE = '3'
		SELECT @VSDE_ORDE = ' ORDER BY 18, 1, 17, 3, 16, 6, 5, 7 ' 
	ELSE
		SELECT @VSDE_ORDE = ' ORDER BY 18, 1, 17, 3, 16, 5, 6, 7 '

	INSERT INTO #TWDOCU_CLIE_Q37_3 ( CO_EMPR, TI_CLIE, CO_CLIE, 
	CO_MONE, IM_SAIN, IM_SAIN_CONV, IM_CXCB, IM_PAGO, IM_CONV, 
	IM_CONV_TOTA, TI_DETA_TICL, TI_DETA_EMPR, TI_DETA_GENE)
	SELECT  T1.CO_EMPR, T1.TI_CLIE, T1.CO_CLIE, 
		T1.CO_MONE, ISNULL(MAX(T3.IM_SAIN), 0), ISNULL(MAX(T3.IM_SAIN_CONV), 0), 
		SUM(ISNULL( T1.IM_CXCB, 0 )), SUM(ISNULL( T1.IM_PAGO, 0 )), 
		SUM(ISNULL( T1.IM_CONV, 0 )), ISNULL((SELECT SUM(T11.IM_CONV) FROM #TWDOCU_CLIE_Q37_1 T11
							 WHERE T1.CO_EMPR = T11.CO_EMPR AND T1.CO_CLIE = T11.CO_CLIE
							 GROUP BY T11.CO_EMPR, T11.CO_CLIE), 0) + 
						ISNULL((SELECT SUM(T11.IM_SAIN_CONV) FROM #TWDOCU_CLIE_Q37_2 T11
							 WHERE T1.CO_EMPR = T11.CO_EMPR AND T1.CO_CLIE = T11.CO_CLIE
							 GROUP BY T11.CO_EMPR, T11.CO_CLIE), 0), 
		'1', '1', '1'	FROM 	#TWDOCU_CLIE_Q37_1 T1
    LEFT OUTER JOIN #TWDOCU_CLIE_Q37_2 T3
    ON  T1.CO_EMPR = T3.CO_EMPR
	And T1.TI_CLIE = T3.TI_CLIE
	And T1.CO_CLIE = T3.CO_CLIE
	And T1.CO_MONE = T3.CO_MONE
	GROUP BY T1.CO_EMPR, T1.TI_CLIE, T1.CO_CLIE, T1.CO_MONE
	UNION ALL
	SELECT	T1.CO_EMPR, T1.TI_CLIE, T1.CO_CLIE, T1.CO_MONE, 
		ISNULL(T1.IM_SAIN, 0), ISNULL(T1.IM_SAIN_CONV, 0), 0, 0, 0, 
				(SELECT SUM(ISNULL(T11.IM_SAIN_CONV, 0)) FROM #TWDOCU_CLIE_Q37_2 T11
				 WHERE T1.CO_EMPR = T11.CO_EMPR AND T1.CO_CLIE = T11.CO_CLIE
				 GROUP BY T11.CO_EMPR, T11.CO_CLIE), 
		'1', '1', '1'
	FROM 	#TWDOCU_CLIE_Q37_2 T1
	WHERE 	NOT EXISTS (SELECT 	T3.CO_EMPR
				FROM 	#TWDOCU_CLIE_Q37_1 T3
				WHERE 	T1.CO_EMPR = T3.CO_EMPR
				AND 	T1.CO_CLIE = T3.CO_CLIE
				AND 	T1.CO_MONE = T3.CO_MONE )
 	UNION ALL
	SELECT	T1.CO_EMPR, T1.TI_CLIE, (SELECT MAX(CO_CLIE) FROM #TWDOCU_CLIE_Q37_1),
		T1.CO_MONE, ISNULL(MAX(T3.IM_SAIN), 0), ISNULL(MAX(T3.IM_SAIN_CONV), 0), 
		SUM(ISNULL( T1.IM_CXCB, 0 )), SUM(ISNULL( T1.IM_PAGO, 0 )),
		SUM(ISNULL( T1.IM_CONV, 0 )), 0 , '2', '1', '1'
	FROM 	#TWDOCU_CLIE_Q37_1 T1
    LEFT OUTER JOIN (SELECT CO_EMPR, TI_CLIE, CO_MONE,
						  SUM(ISNULL(IM_SAIN, 0)) IM_SAIN, 
						  SUM(ISNULL(IM_SAIN_CONV, 0)) IM_SAIN_CONV 
					FROM #TWDOCU_CLIE_Q37_2 
					GROUP BY CO_EMPR, TI_CLIE, CO_MONE) T3
    ON  T1.CO_EMPR = T3.CO_EMPR
	AND T1.TI_CLIE = T3.TI_CLIE
	AND T1.CO_MONE = T3.CO_MONE
	GROUP BY T1.CO_EMPR,  T1.TI_CLIE, T1.CO_MONE 
	UNION ALL
	SELECT	T1.CO_EMPR, T1.TI_CLIE, (SELECT MAX(CO_CLIE) FROM #TWDOCU_CLIE_Q37_1),
		T1.CO_MONE, ISNULL(MAX(T3.IM_SAIN), 0), ISNULL(MAX(T3.IM_SAIN_CONV), 0),
		0, 0, 0, 0, '2', '1', '1'
	FROM 	#TWDOCU_CLIE_Q37_2 T1
    LEFT OUTER JOIN (SELECT CO_EMPR, TI_CLIE, CO_MONE,
						  SUM(ISNULL(IM_SAIN, 0)) IM_SAIN, 
						  SUM(ISNULL(IM_SAIN_CONV, 0)) IM_SAIN_CONV 
					FROM #TWDOCU_CLIE_Q37_2 
					GROUP BY CO_EMPR, TI_CLIE, CO_MONE)T3
    ON  T1.CO_EMPR = T3.CO_EMPR
	AND T1.TI_CLIE = T3.TI_CLIE
	AND T1.CO_MONE = T3.CO_MONE
	AND NOT EXISTS (SELECT 	T3.CO_EMPR
			FROM 	#TWDOCU_CLIE_Q37_1 T3
			WHERE 	T1.CO_EMPR = T3.CO_EMPR
			AND 	T1.TI_CLIE = T3.TI_CLIE
			AND 	T1.CO_MONE = T3.CO_MONE )
	GROUP BY T1.CO_EMPR,  T1.TI_CLIE, T1.CO_MONE 
	UNION ALL
	SELECT	T1.CO_EMPR, MAX(T1.TI_CLIE), MAX(T1.CO_CLIE), MAX(T1.CO_MONE), 
		0, ISNULL(MAX(T3.IM_SAIN_CONV), 0), SUM(ISNULL( T1.IM_CXCB_CONV, 0 )),
		SUM(ISNULL( T1.IM_PAGO_CONV, 0 )), SUM(ISNULL( T1.IM_CONV, 0 )), 0, 
		'3', '1', '1'
	FROM 	#TWDOCU_CLIE_Q37_1 T1
    LEFT OUTER JOIN (SELECT CO_EMPR, TI_CLIE, SUM(ISNULL(IM_SAIN_CONV, 0)) IM_SAIN_CONV 
					FROM #TWDOCU_CLIE_Q37_2 
					GROUP BY CO_EMPR, TI_CLIE) T3
    ON  T1.CO_EMPR = T3.CO_EMPR
	AND	T1.TI_CLIE = T3.TI_CLIE
	GROUP BY T1.CO_EMPR, T1.TI_CLIE
	UNION ALL
	SELECT	T1.CO_EMPR, MAX(T1.TI_CLIE), (SELECT MAX(CO_CLIE) FROM #TWDOCU_CLIE_Q37_1), 
		T1.CO_MONE, ISNULL(MAX(T3.IM_SAIN), 0), ISNULL(MAX(T3.IM_SAIN_CONV), 0),
		SUM(ISNULL( T1.IM_CXCB, 0 )), SUM(ISNULL( T1.IM_PAGO, 0 )), 
		SUM(ISNULL( T1.IM_CONV, 0 )), 0, '2', '2', '1'
	FROM 	#TWDOCU_CLIE_Q37_1 T1
    LEFT OUTER JOIN (SELECT CO_EMPR, CO_MONE, SUM(ISNULL(IM_SAIN, 0)) IM_SAIN, 
						  SUM(ISNULL(IM_SAIN_CONV, 0)) IM_SAIN_CONV 
					FROM #TWDOCU_CLIE_Q37_2 
					GROUP BY CO_EMPR, CO_MONE) T3
    ON  T1.CO_EMPR = T3.CO_EMPR
	AND T1.CO_MONE = T3.CO_MONE
	GROUP BY T1.CO_EMPR, T1.CO_MONE 
	UNION ALL
	SELECT	T1.CO_EMPR, MAX(T1.TI_CLIE), (SELECT MAX(CO_CLIE) FROM #TWDOCU_CLIE_Q37_1),
		T1.CO_MONE, ISNULL(MAX(T3.IM_SAIN), 0), ISNULL(MAX(T3.IM_SAIN_CONV), 0),
		0, 0, 0, 0, '2', '2', '1'
	FROM 	#TWDOCU_CLIE_Q37_2 T1
    LEFT OUTER JOIN (SELECT CO_EMPR, CO_MONE, SUM(ISNULL(IM_SAIN, 0)) IM_SAIN, 
						  SUM(ISNULL(IM_SAIN_CONV, 0)) IM_SAIN_CONV 
					FROM #TWDOCU_CLIE_Q37_2 
					GROUP BY CO_EMPR, CO_MONE)T3
    ON  T1.CO_EMPR = T3.CO_EMPR
	AND T1.CO_MONE = T3.CO_MONE
	AND NOT EXISTS (SELECT 	T3.CO_EMPR
			FROM 	#TWDOCU_CLIE_Q37_1 T3
			WHERE 	T1.CO_EMPR = T3.CO_EMPR
			AND 	T1.CO_MONE = T3.CO_MONE )
	GROUP BY T1.CO_EMPR, T1.CO_MONE
	UNION ALL
	SELECT	T1.CO_EMPR, MAX(T1.TI_CLIE), MAX(T1.CO_CLIE), MAX(T1.CO_MONE), 
		0, ISNULL(MAX(T3.IM_SAIN_CONV), 0), SUM(ISNULL( T1.IM_CXCB_CONV, 0 )), 
		SUM(ISNULL( T1.IM_PAGO_CONV, 0 )), SUM(ISNULL( T1.IM_CONV, 0 )), 0,
		'3', '3', '1'
	FROM 	#TWDOCU_CLIE_Q37_1 T1
    LEFT OUTER JOIN (SELECT CO_EMPR, SUM(ISNULL(IM_SAIN_CONV, 0)) IM_SAIN_CONV 
					FROM #TWDOCU_CLIE_Q37_2 
					GROUP BY CO_EMPR ) T3
    ON  T1.CO_EMPR = T3.CO_EMPR
	GROUP BY T1.CO_EMPR
	UNION ALL
	SELECT	MAX(T1.CO_EMPR), MAX(T1.TI_CLIE), (SELECT MAX(CO_CLIE) FROM #TWDOCU_CLIE_Q37_1),
		T1.CO_MONE, ISNULL(MAX(T3.IM_SAIN), 0), ISNULL(MAX(T3.IM_SAIN_CONV), 0),
		SUM(ISNULL( T1.IM_CXCB, 0 )), SUM(ISNULL( T1.IM_PAGO, 0 )), SUM(ISNULL( T1.IM_CONV, 0 )),  
		0, '2', '2', '2'
	FROM 	#TWDOCU_CLIE_Q37_1 T1
    LEFT OUTER JOIN (SELECT CO_MONE, SUM(ISNULL(IM_SAIN, 0)) IM_SAIN, 
						  SUM(ISNULL(IM_SAIN_CONV, 0)) IM_SAIN_CONV 
					FROM #TWDOCU_CLIE_Q37_2 
					GROUP BY CO_MONE) T3
    ON  T1.CO_MONE = T3.CO_MONE
	GROUP BY T1.CO_MONE 
	UNION ALL
	SELECT	MAX(T1.CO_EMPR), MAX(T1.TI_CLIE), (SELECT MAX(CO_CLIE) FROM #TWDOCU_CLIE_Q37_1),
		T1.CO_MONE, ISNULL(MAX(T3.IM_SAIN), 0), ISNULL(MAX(T3.IM_SAIN_CONV), 0), 0, 0, 0, 
		0, '2', '2', '2'
	FROM 	#TWDOCU_CLIE_Q37_2 T1
    LEFT OUTER JOIN (SELECT CO_MONE, SUM(ISNULL(IM_SAIN, 0)) IM_SAIN, 
						  SUM(ISNULL(IM_SAIN_CONV, 0)) IM_SAIN_CONV 
					FROM #TWDOCU_CLIE_Q37_2 
					GROUP BY CO_MONE)T3
    ON  T1.CO_MONE = T3.CO_MONE
	AND NOT EXISTS (SELECT 	T3.CO_EMPR
			FROM 	#TWDOCU_CLIE_Q37_1 T3
			WHERE 	T1.CO_MONE = T3.CO_MONE )
	GROUP BY T1.CO_MONE
	UNION ALL
	SELECT	MAX(T1.CO_EMPR), MAX(T1.TI_CLIE), MAX(T1.CO_CLIE),MAX(T1.CO_MONE), 
		0, ISNULL((SELECT SUM(ISNULL(IM_SAIN_CONV, 0))
			FROM #TWDOCU_CLIE_Q37_2 
			GROUP BY CO_EMPR), 0),
		SUM(ISNULL( T1.IM_CXCB_CONV, 0 )), SUM(ISNULL( T1.IM_PAGO_CONV, 0 )), 
		0, 0, '3', '3', '3'
	FROM 	#TWDOCU_CLIE_Q37_1 T1


	EXECUTE('SELECT T1.CO_EMPR,  T2.DE_NOMB, T1.TI_CLIE, T4.DE_TIPO_CLIE, T1.CO_CLIE, T4.NO_AUXI,
			T1.CO_MONE, T3.SB_MONE, T3.DE_MONE, T1.IM_SAIN, T1.IM_SAIN_CONV, T1.IM_CXCB, 
			T1.IM_PAGO, T1.IM_CONV, T1.IM_CONV_TOTA, 
			T1.TI_DETA_TICL, T1.TI_DETA_EMPR, T1.TI_DETA_GENE, ""
		FROM #TWDOCU_CLIE_Q37_3 T1
        INNER JOIN TMEMPR T2
        ON AND T1.CO_EMPR = T2.CO_EMPR
        INNER JOIN TMAUXI_EMPR T4
        ON  T4.TI_AUXI_EMPR = "' + @ISTI_AUXI_CLIE + '"
		AND T1.CO_EMPR = T4.CO_EMPR
		AND T1.CO_CLIE = T4.CO_AUXI_EMPR
        LEFT OUTER JOIN TTMONE T3
        ON T1.CO_MONE = T3.CO_MONE
		WHERE (T1.IM_SAIN != 0
		OR T1.IM_SAIN_CONV != 0
		OR T1.IM_CXCB != 0 
		OR T1.IM_PAGO != 0)' + @VSDE_ORDE)

END

RETURN
/*------------------------- FIN ------------------------------ */


GO
